package coollemon.dataBase;

import java.sql.*;
import java.util.ArrayList;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import coollemon.kernel.*;

public class DatabaseControl {
	private Connection conn;
	private String tableName;

	private DatabaseControl(String databseUser, String databasePassword,
			String database, String port, String tableName) throws SQLException {
		conn = Connect.getConnection(databseUser, databasePassword, database,
				port);
		this.tableName = tableName;
		if(!isExistTable())
			this.createTable();
	}

	private static DatabaseControl dbControl = null;

	public static DatabaseControl getInstance(String databseUser,
			String databasePassword, String database, String port,
			String tableName) {
		if (dbControl != null) {
			dbControl.close();
		}
		try {
			dbControl = new DatabaseControl(databseUser, databasePassword,
					database, port, tableName);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return dbControl;
	}

	public ArrayList<String> getTableName() throws SQLException {
		String sql = "SHOW TABLES;";
		Statement stmt = conn.createStatement();
		ResultSet result = stmt.executeQuery(sql);
		ArrayList<String> names = new ArrayList<String>();

		while (result.next())
			names.add(result.getString(1));
		return names;
	}

	private ArrayList<String> getField() throws SQLException{
		String sql = "DESCRIBE "+tableName+";";
		Statement stmt = conn.createStatement();
		ResultSet result = stmt.executeQuery(sql);
		ArrayList<String> fields = new ArrayList<String>();

		while (result.next())
			fields.add(result.getString(1));
		return fields;
	}
	
	private boolean isExistTable() throws SQLException {
		ArrayList<String> names = getTableName();
		for (int i = 0; i < names.size(); i++)
			if (names.get(i).equals(tableName))
				return true;
		return false;
	}
	
	private boolean isLegalTable() throws SQLException{
		ArrayList<String> fields = getField();
		if(!fields.get(0).equals("id"))
			return false;
		if(!fields.get(1).equals("name"))
			return false;
		if(!fields.get(2).equals("phoneNumber"))
			return false;
		if(!fields.get(3).equals("fixedTel"))
			return false;
		if(!fields.get(4).equals("email"))
			return false;
		if(!fields.get(5).equals("qq"))
			return false;
		if(!fields.get(6).equals("nick"))
			return false;
		if(!fields.get(7).equals("sex"))
			return false;
		if(!fields.get(8).equals("birthday"))
			return false;
		if(!fields.get(9).equals("icon"))
			return false;
		if(!fields.get(10).equals("address"))
			return false;
		if(!fields.get(11).equals("workplace"))
			return false;
		if(!fields.get(12).equals("zipCode"))
			return false;
		if(!fields.get(13).equals("homePage"))
			return false;
		if(!fields.get(14).equals("otherWay"))
			return false;	
		if(!fields.get(15).equals("normal"))
			return false;
		if(!fields.get(16).equals("relation"))
			return false;
		
		return true;
	}
	public ContactManager useDatabase() throws Exception {
		ContactManager conM;
		if(isExistTable()){
			conM = new ContactManager();
		}
		else{
			conM = importContacts();
		}
		return conM;
	}

	public boolean createTable() {
		String sql = "CREATE TABLE IF NOT EXISTS "
				+ tableName
				+ " (id BIGINT,name VARCHAR(50), phoneNumber VARCHAR(30), "
				+ "fixedTel VARCHAR(30), email VARCHAR(50), qq VARCHAR(20),"
				+ " nick VARCHAR(50),sex CHAR(1), birthday VARCHAR(10), "
				+ "icon VARCHAR(100), address VARCHAR(100), workplace VARCHAR(100), "
				+ "zipCode VARCHAR(10), homePage VARCHAR(100),otherWay VARCHAR(100), "
				+ "normal VARCHAR(100), relation VARCHAR(100));";
		PreparedStatement stmt = null;
		try {
			stmt = conn.prepareStatement(sql);
			if (stmt != null)
				stmt.executeUpdate();
			else
				return false;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}

		return true;
	}

	public boolean insertContact(Contact contact) {
		long id = contact.getId();
		String name = contact.getName();
		String phoneNum = contact.getPhoneNumber();
		String fixedTel = contact.getFixedTel();
		String email = contact.getEmail();
		String qq = contact.getQq();
		String nick = contact.getNick();
		String sex;
		switch (contact.getSex()) {
		case 1:
			sex = "M";
			break;
		case 2:
			sex = "F";
			break;
		default:
			sex = "";
			break;
		}
		String birthday = contact.getBirthday().toString();
		String icon = contact.getIcon();
		String addr = contact.getAddress();
		String workplace = contact.getWorkplace();
		String zipCode = contact.getZipCode();
		String homepage = contact.getHomepage();

		ArrayList<String> otherWays = contact.getOtherWay();
		String otherWayStr = "";
		if (otherWays.size() > 0) {
			for (int j = 0; j < otherWays.size() - 1; j++) {
				otherWayStr += otherWays.get(j);
				otherWayStr += ";";
			}
			otherWayStr += otherWays.get(otherWays.size() - 1);
		}

		ArrayList<NormalTag> normals = contact.getNormal();
		String normalStr = "";
		if (normals.size() > 0) {
			for (int j = 0; j < normals.size() - 1; j++) {
				normalStr += normals.get(j).getName();
				normalStr += ";";
			}
			normalStr += normals.get(normals.size() - 1).getName();
		}

		ArrayList<RelationTag> relations = contact.getRelation();
		String relationStr = "";

		for (int j = 0; j < relations.size(); j++) {
			RelationTag relationTag = relations.get(j);
			ArrayList<Pair<Contact, Contact>> pairs = relationTag.getContacts();
			for (int k = 0; k < pairs.size(); k++) {
				relationStr = relationStr + "<" + relationTag.getName() + ","
						+ pairs.get(k).getFirst().getName() + ","
						+ pairs.get(k).getSecond().getName() + ">" + ";";
			}
		}
		if (!relationStr.isEmpty())
			relationStr = relationStr.substring(0, relationStr.length() - 1);

		String sql = "INSERT INTO " + tableName + " VALUES (" + "'" + id
				+ "','" + name + "','" + phoneNum + "','" + fixedTel + "','"
				+ email + "','" + qq + "','" + nick + "','" + sex + "','"
				+ birthday + "','" + icon + "','" + addr + "','" + workplace
				+ "','" + zipCode + "','" + homepage + "','" + otherWayStr
				+ "','" + normalStr + "','" + relationStr + "');";

		PreparedStatement stmt = null;
		try {
			stmt = conn.prepareStatement(sql);
			if (stmt != null)
				stmt.executeUpdate();
			else
				return false;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}

	public boolean updateContact(Contact contact) {

		long id = contact.getId();
		String name = contact.getName();
		String phoneNum = contact.getPhoneNumber();
		String fixedTel = contact.getFixedTel();
		String email = contact.getEmail();
		String qq = contact.getQq();
		String nick = contact.getNick();
		String sex;
		switch (contact.getSex()) {
		case 1:
			sex = "M";
			break;
		case 2:
			sex = "F";
			break;
		default:
			sex = "";
			break;
		}
		String birthday = contact.getBirthday().toString();
		String icon = contact.getIcon();
		String addr = contact.getAddress();
		String workplace = contact.getWorkplace();
		String zipCode = contact.getZipCode();
		String homepage = contact.getHomepage();

		ArrayList<String> otherWays = contact.getOtherWay();
		String otherWayStr = "";
		if (otherWays.size() > 0) {
			for (int j = 0; j < otherWays.size() - 1; j++) {
				otherWayStr += otherWays.get(j);
				otherWayStr += ";";
			}
			otherWayStr += otherWays.get(otherWays.size() - 1);
		}

		ArrayList<NormalTag> normals = contact.getNormal();
		String normalStr = "";
		if (normals.size() > 0) {
			for (int j = 0; j < normals.size() - 1; j++) {
				normalStr += normals.get(j).getName();
				normalStr += ";";
			}
			normalStr += normals.get(normals.size() - 1).getName();
		}

		ArrayList<RelationTag> relations = contact.getRelation();
		String relationStr = "";

		for (int j = 0; j < relations.size(); j++) {
			RelationTag relationTag = relations.get(j);
			ArrayList<Pair<Contact, Contact>> pairs = relationTag.getContacts();
			for (int k = 0; k < pairs.size(); k++) {
				relationStr = relationStr + "<" + relationTag.getName() + ","
						+ pairs.get(k).getFirst().getName() + ","
						+ pairs.get(k).getSecond().getName() + ">" + ";";
			}
		}
		if (!relationStr.isEmpty())
			relationStr = relationStr.substring(0, relationStr.length() - 1);

		String sql = "UPDATE " + tableName + " SET name = '" + name
				+ "', phoneNumber = '" + phoneNum + "', fixedTel = '"
				+ fixedTel + "', email = '" + email + "', qq = '" + qq
				+ "', nick = '" + nick + "', sex = '" + sex + "', birthday = '"
				+ birthday + "', icon = '" + icon + "', address = '" + addr
				+ "', workplace = '" + workplace + "', zipCode = '" + zipCode
				+ "', homePage = '" + homepage + "', otherWay = '"
				+ otherWayStr + "', normal = '" + normalStr + "', relation = '"
				+ relationStr + "' WHERE id = '" + id + "';";
		System.out.println(sql);
		PreparedStatement stmt = null;
		try {
			stmt = conn.prepareStatement(sql);
			if (stmt != null)
				stmt.executeUpdate();
			else
				return false;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}

	public boolean deleteContact(Contact contact) {

		long id = contact.getId();
		String sql = "DELETE FROM " + tableName + " WHERE id = " + id + ";";
		System.out.print(sql);
		PreparedStatement stmt = null;
		try {
			stmt = conn.prepareStatement(sql);
			if (stmt != null)
				stmt.executeUpdate();
			else
				return false;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}

	public ContactManager importContacts() throws Exception {
		
		if(!isLegalTable())
			throw new IllegalTableException();
		ArrayList<Contact> contacts = new ArrayList<Contact>();
		String sql = "SELECT * FROM " + tableName + ";";
		Statement stmt;
		try {
			stmt = conn.createStatement();
			ResultSet result = stmt.executeQuery(sql);
			while (result.next()) {
				long id = result.getInt("id");
				String name = result.getString("name");
				String phoneNum = result.getString("phoneNumber");
				String fixedTel = result.getString("fixedTel");
				String email = result.getString("email");
				String qq = result.getString("qq");
				String nick = result.getString("nick");
				String sexStr = result.getString("sex");
				String birthdayStr = result.getString("birthday");
				String icon = result.getString("icon");
				String addr = result.getString("address");
				String workplace = result.getString("workplace");
				String zipCode = result.getString("zipCode");
				String homePage = result.getString("homePage");
				String others = result.getString("otherWay");
				String normals = result.getString("normal");
				String relations = result.getString("relation");

				int sex = 0;
				if (sexStr.equalsIgnoreCase("M"))
					sex = 1;
				else if (sexStr.equalsIgnoreCase("F"))
					sex = 2;
				else
					;

				BirthDay birth = new BirthDay(birthdayStr);

				ArrayList<String> otherWay = new ArrayList<String>();
				String regex = "([^;]+);*";
				Pattern pattern = Pattern.compile(regex);
				Matcher matcher = pattern.matcher(others);
				while (matcher.find()) {
					String str = matcher.group(1);
					otherWay.add(str);
				}

				ArrayList<NormalTag> normal = new ArrayList<NormalTag>();
				matcher = pattern.matcher(normals);
				while (matcher.find()) {
					String str = matcher.group(1);
					normal.add(NormalTag.createNormalTag(str));
				}

				Contact contact = Contact.createContact(name, phoneNum,
						fixedTel, email, qq, nick, sex, birth, icon, addr,
						workplace, zipCode, homePage, otherWay, normal, null);
				contacts.add(contact);

				regex = "(<([^;]+),([^;]+),([^;]+)>);*";
				pattern = Pattern.compile(regex);
				matcher = pattern.matcher(relations);
				while (matcher.find()) {
					String tag = matcher.group(2);
					String name1 = matcher.group(3);
					String name2 = matcher.group(4);

					RelationTag relationTag = RelationTag
							.createRelationTag(tag);

					ArrayList<Contact> B = new ArrayList<Contact>();
					for (int j = 0; j < contacts.size(); j++) {
						if (contacts.get(j).getName().equals(name2))
							B.add(contacts.get(j));
					}
					if (contact.getName().equals(name1)) {
						for (int k = 0; k < B.size(); k++) {
							ContactManager.addTagToContact(
									new Pair<Contact, Contact>(contact, B
											.get(k)), relationTag);
						}
					}

				}
			}
			result.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return new ContactManager(contacts);
	}

	public boolean exportContacts(ContactManager conM) {
		String sql = "DELETE FROM " + tableName + ";";
		PreparedStatement stmt = null;
		try {
			stmt = conn.prepareStatement(sql);
			if (stmt != null)
				stmt.executeUpdate();
			else
				return false;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}

		ArrayList<Contact> contacts = conM.getContacts();
		for (int i = 0; i < contacts.size(); i++) {
			Contact contact = contacts.get(i);
			if (!insertContact(contact))
				return false;
		}

		return true;
	}

	public boolean dropTable() {
		String sql = "DROP TABLE " + tableName + ";";
		PreparedStatement stmt = null;
		try {
			stmt = conn.prepareStatement(sql);
			if (stmt != null)
				stmt.executeUpdate();
			else
				return false;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}

	public void close() {
		try {
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static void main(String[] args) throws Exception {
		DatabaseControl dc = DatabaseControl.getInstance("root", "tsinghua",
				"coollemon", "3308", "test");
		dc.useDatabase();
	}
}
